package dao;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import pojo.Chitiethoadon;
import pojo.ChitiethoadonId;
import pojo.Dotuoidoanhthu;
import pojo.Hinhthucthanhtoan;
import pojo.Hoadon;
import pojo.Loainguoidungdoanhthu;
import pojo.Sanpham;
import pojo.Taikhoan;
import util.ConvertHelper;
import util.HibernateUtil;

public class HoadonDAO extends AbstractDAO<Hoadon, Integer> {

	@Override
	protected Class getPOJOClass() {
		// TODO Auto-generated method stub
		return Hoadon.class;
	}

	@Override
	protected Query MakeHQL() {
		// TODO Auto-generated method stub
		return null;
	}

	public List<Hoadon> GetHoaDon(int maTaiKhoan) {
		String hql = "select hd from Hoadon hd where hd.taikhoan.mataikhoan="
				+ maTaiKhoan;

		Session session = HibernateUtil.getSessionFactory().openSession();
		Query selectClause = session.createQuery(hql);

		List<Hoadon> dsHoaDon = selectClause.list();

		// System.out.println(ds.get(0).toString());

		session.close();
		return dsHoaDon;
	}

	public int GetMaHoaDonLonNhat() {
		String hql = "select max(mahoadon) from Hoadon";

		Session session = HibernateUtil.getSessionFactory().openSession();
		Query selectClause = session.createQuery(hql);

		List<Integer> ds = selectClause.list();

		System.out.println(ds.get(0).toString());

		session.close();
		return ds.get(0);
	}

	public int ThemHoaDonMoi(String diaChiNguoiNhan, int hinhThucThanhToan,
			Taikhoan taiKhoan, List<Sanpham> gioHang) {

		// ArrayList<Sanpham> ds = giohang;

		HoadonDAO dao = new HoadonDAO();
		// Hien tai = Max + 1
		int maHoaDonHienTai = dao.GetMaHoaDonLonNhat() + 1;

		Hinhthucthanhtoan httt = (new HinhthucthanhtoanDAO()).getList().get(
				hinhThucThanhToan);
		String diachinhanhang = diaChiNguoiNhan;

		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		Date date = new Date();
		System.out.println(dateFormat.format(date));

		// Hoadon hoadon = dao.get(maHoaDonHienTai);
		Hoadon hd = new Hoadon();
		hd.setMahoadon(maHoaDonHienTai);
		hd.setHinhthucthanhtoan(httt);
		hd.setTaikhoan(taiKhoan);
		hd.setDiachinhanhang(diachinhanhang);
		hd.setNgayxuat(date);
		hd.setDaduyet(false);
		hd.setDaxoa(false);

		int tongTien = 0;
		for (int i = 0; i < gioHang.size(); i++) {
			/*tongTien += gioHang.get(i).getGiaban() / 1000
					* gioHang.get(i).getSoluongtronggiohang();*/
			tongTien += gioHang.get(i).getGiaban()
					* gioHang.get(i).getSoluongtronggiohang();
		}
		hd.setTongtien(tongTien);

		// LÆ°u hoÌ�a Ä‘Æ¡n
		// ----------------------
		Session hiberSession = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = hiberSession.beginTransaction();

		hiberSession.save(hd);
		tx.commit();
		System.out.println("Done HoaDon");
		// ---------------------

		// TaÌ£o chi tiÃªÌ�t hoÌ�a Ä‘Æ¡n
		Chitiethoadon cthd = null;
		ChitiethoadonId cthdid = null;
		Sanpham sp = null;

		tx = hiberSession.beginTransaction();
		for (int i = 0; i < gioHang.size(); i++) {

			sp = gioHang.get(i);

			cthdid = new ChitiethoadonId();
			cthdid.setMahoadon(maHoaDonHienTai);
			cthdid.setMasp(sp.getMasanpham());

			cthd = new Chitiethoadon();
			//cthd.setDongia((sp.getGiaban()) / 1000);
			cthd.setDongia((sp.getGiaban()));
			cthd.setHoadon(hd);
			cthd.setId(cthdid);
			cthd.setSanpham(sp);
			cthd.setSoluong(sp.getSoluongtronggiohang());

			// hiberSession.save(cthdid);
			hiberSession.save(cthd);

			sp.setSoluongban(sp.getSoluongban() + sp.getSoluongtronggiohang());
			sp.setSoluongtrongkho(sp.getSoluongtrongkho() - sp.getSoluongtronggiohang());
			hiberSession.update(sp);
		}
		tx.commit();

		hiberSession.close();
		return maHoaDonHienTai;
	}

	public int ThemHoaDonMoi(HttpServletRequest request, HttpSession session) {

		ArrayList<Sanpham> ds = (ArrayList<Sanpham>) session
				.getAttribute("gioHang");

		HoadonDAO dao = new HoadonDAO();
		// Hien tai = Max + 1
		int maHoaDonHienTai = dao.GetMaHoaDonLonNhat() + 1;

		// TaÌ£o HoÌ�a Ä‘Æ¡n
		String s = request.getParameter("ptThanhToan");
		int htttIndex = Integer.valueOf(s);

		Hinhthucthanhtoan httt = (new HinhthucthanhtoanDAO()).getList().get(
				htttIndex);
		Taikhoan tk = (new TaikhoanDAO()).get(((Taikhoan) (session
				.getAttribute("taiKhoan"))).getMataikhoan());
		String diachinhanhang = ConvertHelper.GetRequestUnicodeString(request,
				"diaChiNguoiNhan");

		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		Date date = new Date();
		System.out.println(dateFormat.format(date));

		// Hoadon hoadon = dao.get(maHoaDonHienTai);
		Hoadon hd = new Hoadon();
		hd.setMahoadon(maHoaDonHienTai);
		hd.setHinhthucthanhtoan(httt);
		hd.setTaikhoan(tk);
		hd.setDiachinhanhang(diachinhanhang);
		hd.setNgayxuat(date);
		hd.setDaduyet(false);

		// LÆ°u hoÌ�a Ä‘Æ¡n
		// ----------------------
		Session hiberSession = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = hiberSession.beginTransaction();

		hiberSession.save(hd);
		tx.commit();
		System.out.println("Done HoaDon");
		// ---------------------

		// TaÌ£o chi tiÃªÌ�t hoÌ�a Ä‘Æ¡n
		Chitiethoadon cthd = null;
		ChitiethoadonId cthdid = null;
		Sanpham sp = null;

		tx = hiberSession.beginTransaction();
		for (int i = 0; i < ds.size(); i++) {

			sp = ds.get(i);

			cthdid = new ChitiethoadonId();
			cthdid.setMahoadon(maHoaDonHienTai);
			cthdid.setMasp(sp.getMasanpham());

			cthd = new Chitiethoadon();
			//cthd.setDongia((sp.getGiaban()) / 1000);
			cthd.setDongia((sp.getGiaban()));
			cthd.setHoadon(hd);
			cthd.setId(cthdid);
			cthd.setSanpham(sp);
			cthd.setSoluong(sp.getSoluongtronggiohang());

			// hiberSession.save(cthdid);
			hiberSession.save(cthd);

			sp.setSoluongban(sp.getSoluongban() + sp.getSoluongtronggiohang());
			hiberSession.update(sp);
		}
		tx.commit();

		return maHoaDonHienTai;
	}

	// ----------------------------------------
	public List<Hoadon> GetHoaDon(Sanpham sp) {
		String hql = "select cthd.hoadon from Chitiethoadon cthd"
				+ " where cthd.sanpham.masanpham = " + sp.getMasanpham();

		Session session = HibernateUtil.getSessionFactory().openSession();
		Query selectClause = session.createQuery(hql);

		List<Hoadon> dsHoaDon = selectClause.list();
		// System.out.println(ds.get(0).toString());
		session.close();
		return dsHoaDon;
	}

	public List<Hoadon> GetHoaDonTheoMaLoaiVaTrangThai(int maLoaiSanPham,
			int trangThaiDuyet, int trangThaiXoa) {

		String hql = "select cthd.hoadon from Chitiethoadon cthd ";
		if (-1 == maLoaiSanPham) {
			if (-1 != trangThaiDuyet || -1 != trangThaiXoa)
				hql = hql + " where ";
		} else
			hql = hql + " where cthd.sanpham.loaisanpham.maloaisanpham = "
					+ maLoaiSanPham;

		if (0 == trangThaiDuyet)
			hql = hql + " and cthd.hoadon.daduyet = false";
		else if (1 == trangThaiDuyet)
			hql = hql + " and cthd.hoadon.daduyet = true";

		if (0 == trangThaiXoa)
			hql = hql + " and cthd.hoadon.daxoa = false";
		else if (1 == trangThaiXoa)
			hql = hql + " and cthd.hoadon.daxoa = true";

		// xóa bớt nếu có gặp trường hợp dư thừa
		hql = hql.replace("  ", " ").replace("where and", "where");

		// --------------
		System.out.println(hql);
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query selectClause = session.createQuery(hql);
		// --------------

		List<Hoadon> lstHoaDon = selectClause.list();
		System.out.println(lstHoaDon.size());
		session.close();
		return lstHoaDon;
	}

	public List<Loainguoidungdoanhthu> GetKetQuaThongKeDoanhThuTheoNguoiDung(
			String loaiNguoiDung, String loaiThoiGian, int khoangThoiGian,
			String thoiGianTu, String thoiGianDen) {

		List<Integer> lstMaTaiKhoanAdmin;
		// ----------------------
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = null;
		String hql = "";
		// ----------------------

		// Get mã tài khoản admin
		hql = "select tka.mataikhoan from Taikhoanadmin tka";
		query = session.createQuery(hql);
		lstMaTaiKhoanAdmin = query.list();

		hql = "select sum(hd.tongtien) from Hoadon hd where";
		if (loaiNguoiDung.equalsIgnoreCase("member")) {
			hql = hql + " hd.taikhoan.mataikhoan not in :lstMaTaiKhoanAdmin ";
		} else if (loaiNguoiDung.equalsIgnoreCase("admin")) {
			hql = hql + " hd.taikhoan.mataikhoan in :lstMaTaiKhoanAdmin ";
		}

		// nếu khoảng thời gian là 0 -> tìm kiếm theo thoiGianTu và thoiGianDen
		if (loaiThoiGian.equalsIgnoreCase("MocThoiGian")) {
			hql = hql + " and hd.ngayxuat >= :thoiGianTu "
					+ " and hd.ngayxuat <= :thoiGianDen ";
		} else if (loaiThoiGian.equalsIgnoreCase("KhoangThoiGian")
				&& -1 != khoangThoiGian) {
			hql = hql
					+ " and hd.ngayxuat >= :nowDateWithKhoangThoiGian ";
		}

		hql = hql.replace("  ", " ").replace("where and", "where");
		System.out.println(hql);
		// ----------------------
		// Session session = HibernateUtil.getSessionFactory().openSession();
		// Query query = null;
		query = session.createQuery(hql);

		// Set Param cho lstMaTaiKhoanAdmin
		try {
			query.setParameterList("lstMaTaiKhoanAdmin", lstMaTaiKhoanAdmin);
		} catch (Exception ex) {
			System.out.println("Err lstMaTaiKhoanAdmin");
		}
		// Set Param cho KhoangThoiGian
		try {
			Calendar now = Calendar.getInstance();
			now.add(Calendar.MONTH, -khoangThoiGian);

			query.setParameter("nowDateWithKhoangThoiGian", now.getTime());
		} catch (Exception ex) {
			System.out.println("Err nowDateWithKhoangThoiGian");
		}
		// Set Param cho thoiGianTu, thoiGianDen
		try {
			query.setParameter("thoiGianTu",
					ConvertHelper.ConvertStringToDate(thoiGianTu));
			query.setParameter("thoiGianDen",
					ConvertHelper.ConvertStringToDate(thoiGianDen));
		} catch (Exception ex) {
			System.out.println("Err thoiGianTu-thoiGianDen");
		}

		// query.setFirstResult(0);
		// query.setMaxResults(nTop);
		List<Long> queryResult = query.list();
		session.close();
		// ----------------------
		List<Loainguoidungdoanhthu> result = new ArrayList<Loainguoidungdoanhthu>();
		if (0 < queryResult.size()) {
			Loainguoidungdoanhthu lnddt = new Loainguoidungdoanhthu();
			lnddt.setTenloai(loaiNguoiDung);
			//lnddt.setTongdoanhthu((long)queryResult.get(0)[0]);
			lnddt.setTongdoanhthu(queryResult.get(0) == null ? 0 : queryResult.get(0));
			result.add(lnddt);
		}
		return result;
	}
	

	public List<Dotuoidoanhthu> GetKetQuaThongKeDoanhThuTheoDoTuoi(
			String doTuoi, String loaiThoiGian, int khoangThoiGian,
			String thoiGianTu, String thoiGianDen) {

		String tenDoTuoi = "";
		// ----------------------
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = null;
		String hql = "";
		// ----------------------

		hql = hql + "select sum(hd.tongtien) from Hoadon hd " +
				" where ";
		// Xác định độ tuổi:
		if (doTuoi.equalsIgnoreCase("<=20"))
		{
			tenDoTuoi = "Nhỏ hơn 20";
			hql = hql + " :namHienTai - year(hd.taikhoan.ngaysinh) <= 20 ";
		} else if (doTuoi.equalsIgnoreCase("<=30"))
		{
			tenDoTuoi = "Từ 20 đến 30";
			hql = hql + " :namHienTai - year(hd.taikhoan.ngaysinh) > 20 " +
					"and :namHienTai - year(hd.taikhoan.ngaysinh) <= 30 ";
		} else if (doTuoi.equalsIgnoreCase("<=40"))
		{
			tenDoTuoi = "Từ 30 đến 40";
			hql = hql + " :namHienTai - year(hd.taikhoan.ngaysinh) > 30 " +
					"and :namHienTai - year(hd.taikhoan.ngaysinh) <= 40 ";
		} else if (doTuoi.equalsIgnoreCase(">40"))
		{
			tenDoTuoi = "Lớn hơn 40";
			hql = hql + " :namHienTai - year(hd.taikhoan.ngaysinh) > 40 ";
		}
		
		
		//------------------------------
		// nếu khoảng thời gian là 0 -> tìm kiếm theo thoiGianTu và thoiGianDen
		if (loaiThoiGian.equalsIgnoreCase("MocThoiGian")) {
			hql = hql + " and hd.ngayxuat >= :thoiGianTu "
					+ " and hd.ngayxuat <= :thoiGianDen ";
		} else if (loaiThoiGian.equalsIgnoreCase("KhoangThoiGian")
				&& -1 != khoangThoiGian) {
			hql = hql
					+ " and hd.ngayxuat >= :nowDateWithKhoangThoiGian ";
		}

		hql = hql.replace("  ", " ").replace("where and", "where");
		System.out.println(hql);
		// ----------------------
		// Session session = HibernateUtil.getSessionFactory().openSession();
		// Query query = null;
		query = session.createQuery(hql);

		// Set Param cho lstMaTaiKhoanAdmin
		try {

			Calendar now = Calendar.getInstance();
			int namHienTai = now.YEAR;
			query.setParameter("namHienTai", namHienTai);
		} catch (Exception ex) {
			System.out.println("Err namHienTai");
		}
		// Set Param cho KhoangThoiGian
		try {
			Calendar now = Calendar.getInstance();
			now.add(Calendar.MONTH, -khoangThoiGian);

			query.setParameter("nowDateWithKhoangThoiGian", now.getTime());
		} catch (Exception ex) {
			System.out.println("Err nowDateWithKhoangThoiGian");
		}
		// Set Param cho thoiGianTu, thoiGianDen
		try {
			query.setParameter("thoiGianTu",
					ConvertHelper.ConvertStringToDate(thoiGianTu));
			query.setParameter("thoiGianDen",
					ConvertHelper.ConvertStringToDate(thoiGianDen));
		} catch (Exception ex) {
			System.out.println("Err thoiGianTu-thoiGianDen");
		}

		// query.setFirstResult(0);
		// query.setMaxResults(nTop);
		List<Long> queryResult = query.list();
		session.close();
		// ----------------------
		List<Dotuoidoanhthu> result = new ArrayList<Dotuoidoanhthu>();
		if (0 < queryResult.size()) {
			Dotuoidoanhthu dtdt = new Dotuoidoanhthu();
			dtdt.setTendotuoi(tenDoTuoi);
			//lnddt.setTongdoanhthu((long)queryResult.get(0)[0]);
			dtdt.setTongdoanhthu(queryResult.get(0) == null ? 0 : queryResult.get(0));
			result.add(dtdt);
		}
		return result;
	}
}
